package com.vcs.frm.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.vcs.frm.dao.mappers.TeacherRowMapper;
import com.vcs.frm.domain.Teacher;

public class SchoolDaoImpl implements SchoolDao {

	Log log = LogFactory.getLog(this.getClass());
	
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedTemplate;


	public SchoolDaoImpl(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.namedTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
	

	@Override
	public int saveTeacher(Teacher teacher) {
		log.info("saveTeacher : " + teacher);
		
		String query = "INSERT INTO TEACHERS(TEACHER_ID, TEACHER_FIRST_NAME, TEACHER_MIDDLE_NAME, TEACHER_LAST_NAME, QUALIFICATIONS ,SPECIAL_COURSES, DESIGNATION_CODE, DATE_OF_JOINING)" +
				" VALUES(:teacherId, :firstName, :middleName, :lastName,:qualifications, :specialCourses, :designationCode, :dateOfJoining)"; 
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("teacherId", teacher.getTeacherId());
		params.put("firstName", teacher.getFirstName());
		params.put("middleName", teacher.getMiddleName());
		params.put("lastName", teacher.getLastName());
		params.put("qualifications", teacher.getQualifications());
		params.put("specialCourses", teacher.getSpecialCourses());
		params.put("designationCode", teacher.getDesignationCode());
		params.put("dateOfJoining", teacher.getDateOfJoining());
		
		// this will hold the generate key
		KeyHolder keyHolder = new GeneratedKeyHolder();
		// parameters source 
		SqlParameterSource paramsSource = new MapSqlParameterSource(params);
		
		int status = namedTemplate.update(query, paramsSource, keyHolder);
		log.info("PRIMARY KEY FOR INSERTED TEACHER RECORD WAS : " + keyHolder.getKey());
		log.info(status);
		return status;
	}

	/**
	 * Lists all teachers in school
	 */
	public List<Teacher> getAllTeachers()
	{
		String query = "SELECT T.TEACHER_ID, T.TEACHER_FIRST_NAME, T.TEACHER_MIDDLE_NAME, T.TEACHER_LAST_NAME, T.QUALIFICATIONS, T.SPECIAL_COURSES, T.DESIGNATION_CODE, D.DESIGNATION, T.DATE_OF_JOINING" +
				" FROM TEACHERS T JOIN DESIGNATIONS D WHERE D.DESIGNATION_CODE = T.DESIGNATION_CODE" +
				" ORDER BY T.TEACHER_ID";
		
		List<Teacher> teachers = new ArrayList<Teacher>();
		teachers = jdbcTemplate.query(query, new TeacherRowMapper());
		log.debug("Fetched list of "+ teachers.size()+ " Teachers");
		return teachers;
	}

	/**
	 * Fetches teacher by teacher id.
	 */
	public Teacher getTeacherByTeacherId(int teacherId)
	{
		String query = "SELECT T.TEACHER_ID, T.TEACHER_FIRST_NAME, T.TEACHER_MIDDLE_NAME, T.TEACHER_LAST_NAME, T.QUALIFICATIONS, T.SPECIAL_COURSES, T.DESIGNATION_CODE, D.DESIGNATION, T.DATE_OF_JOINING" +
				" FROM TEACHERS T JOIN DESIGNATIONS D WHERE D.DESIGNATION_CODE = T.DESIGNATION_CODE AND T.TEACHER_ID= :teacherId" +
				" ORDER BY T.TEACHER_ID";
		//params
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("teacherId", teacherId);
		
		Teacher teacher = new Teacher();
		teacher = namedTemplate.queryForObject(query, params, new TeacherRowMapper());
		log.debug("Teacher fetched : "+ teacher);
		return teacher;
	}

	public int deleteTeacher(int teacherId){
		String deleteSql = "DELETE FROM TEACHERS WHERE TEACHER_ID=:teacherId";
		//params
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("teacherId", teacherId);
		int status = namedTemplate.update(deleteSql, params);
		return status;
	}


}
